// 导入需要的对象
const db = require('../common/mysql')

// console.log(conn)
function queryAll () {
  return new Promise((resolve, reject) => {
    // 执行一个查询
    db.query('select * from user_tel', function (error, results) {
      if (error) reject(error)
      resolve(results)
    })
  })
}

// queryAll();

function queryById (id) {
  db.query('select * from user_tel where id=?', [id], function (error, results, fields) {
    if (error) throw error
    console.log(results)
  })
}
// queryById(1)

function queryByCountUserTel () {
  return new Promise((resolve, reject) => {
    // 执行一个查询
    db.query('select count(t.id) countNum from user_tel t', function (error, results) {
      if (error) reject(error)
      resolve(results)
    })
  })
}
function queryByPageUserTel (offset, rows) {
  return new Promise((resolve, reject) => {
    // 执行一个查询
    db.query('SELECT t.username, t.tel, t.create_time, count(t.username) over (PARTITION BY t.username) phonenum, min(t.create_time) over (PARTITION BY t.username) minbuydate,row_number() over (PARTITION BY t.username order by t.create_time asc) rowNum FROM test.user_tel t limit ?, ?;', [offset, rows], function (error, results) {
      if (error) reject(error)
      resolve(results)
    })
  })
}

function queryByPageAll (offset, rows) {
  return new Promise((resolve, reject) => {
    // 执行一个查询
    db.query('SELECT t.username, t.tel, t.create_time FROM test.user_tel t limit ?, ?;', [offset, rows], function (error, results) {
      if (error) reject(error)
      resolve(results)
    })
  })
}

/**
async function testTransaction () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  const conn = await db.getConn()
  conn.beginTransaction(function (err) {
    if (err) { throw err }
    conn.query(sql, ['u3', '113'], function (error, results, fields) {
      if (error) {
        return conn.rollback(function () {
          throw error
        })
      }

      console.log('do 1')
      // const log = 'Post ' + results.insertId + ' added'

      conn.query(sql, ['u4', '114'], function (error, results, fields) {
        if (error) {
          return conn.rollback(function () {
            throw error
          })
        }
        conn.commit(function (err) {
          if (err) {
            return conn.rollback(function () {
              throw err
            })
          }
          console.log('success!')
        })
      })
    })
  })
}

testTransaction() */
/**
async function testTransaction1 () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  let conn
  try {
    // 1. 获取连接
    conn = await db.getConn()
    // 2.开启事务
    await db.beginTransaction(conn)
    // 3. 执行业务单元
    const r1 = await db.query(conn, sql, ['u6', '116'])
    console.log(r1)
    const r2 = await db.query(conn, sql, ['u7', '117'])
    console.log(r2)
    // 4. 提交
    await db.commit(conn)
    console.log('commit')
  } catch (error) {
    // 5. 回滚
    if (conn) {
      db.rollback(conn)
      console.log('roolback', error)
    }
  }
}

testTransaction1() */
/**
async function testTransaction2 () {
  const sql = 'INSERT INTO `user_tel` ( `username`, `tel`, `create_time`) VALUES ( ?,?,now())'
  db.transaction(async (conn) => {
    // 3. 执行业务单元
    const r1 = await db.query(conn, sql, ['u8', '118'])
    console.log(r1)
    const r2 = await db.query(conn, sql, ['u9', '119'])
    console.log(r2)
  })
}

testTransaction2() */

module.exports = {
  queryAll,
  queryById,
  queryByCountUserTel,
  queryByPageUserTel,
  queryByPageAll
}
